package com.sjq.project.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import com.sjq.project.dao.ProjectBudgetDao;
import com.sjq.common.dao.GenericDao;
import com.sjq.common.util.PageResults;
import com.sjq.project.entity.ProjectBudget;

@Repository
public class ProjectBudgetDaoImpl extends GenericDao<ProjectBudget> implements ProjectBudgetDao {

	 @Override
	 public PageResults<?> selectProjectBudgetByPage(int pageNo, int pageSize,ProjectBudget projectBudget){
	 	StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		/*if (projectBudget.getXxzjbh() != null&& !(projectBudget.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(projectBudget.getXxzjbh().trim());
		}*/
		String sql_fenye="select YFKTXR,YFKTXRSFHM,YFKTXRDWBM,YFKTXRSJHM,SJFKTXR,SJFKTXRSFHM,SJFKTXRDWBM,to_char(YFKTXSJ,'yyyy-MM-dd hh24:mi:ss') as YFKTXSJ,to_char(SJFKTXSJ,'yyyy-MM-dd hh24:mi:ss') as SJFKTXSJ,XMJSJD,SJFKSJ,XXZJBH,GLXMBH,YFKNDH,YFKYF,YFKJE,SJFKJE from T_XM_FKJLB where DJBGBS <>3 " + paramSql.toString() ;
		PageResults<Map<String, String>> results = new PageResults<Map<String, String>>();
		StringBuilder pageSql = new StringBuilder()
				.append("select * from ")
				.append(" (select tmp_1.*, rownum as rowindex from ("+sql_fenye+") tmp_1 ) tmp_2 ")
				.append(" where rowindex between ")
				.append(((pageNo-1)*pageSize + 1))
				.append(" and ")
				.append(((pageNo - 1) * pageSize + pageSize));
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(pageSql.toString(),params.toArray());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql="select count(1) as num from T_XM_FKJLB where DJBGBS <>3  "+paramSql.toString()+" ";
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,params.toArray());
		int count = 0;
		while(sqlRowSet.next()){
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
        results.setPageNo(pageNo);
        results.setPageSize(pageSize);
		results.setResults(list);
		return results;	
	 }
	 @Override
	 public int addProjectBudget(ProjectBudget projectBudget){
	 	String sql="insert into T_XM_FKJLB ( yfktxr,yfktxrsfhm,yfktxrdwbm,yfktxrsjhm,sjfktxr,sjfktxrsfhm,sjfktxrdwbm,yfktxsj,sjfktxsj,xmjsjd,sjfksj,xxzjbh,glxmbh,yfkndh,yfkyf,yfkje,sjfkje) values (?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,?,?,?)  ";
	 	Object [] params={projectBudget.getYfktxr(),projectBudget.getYfktxrsfhm(),projectBudget.getYfktxrdwbm(),projectBudget.getYfktxrsjhm(),projectBudget.getSjfktxr(),projectBudget.getSjfktxrsfhm(),projectBudget.getSjfktxrdwbm(),projectBudget.getYfktxsj(),projectBudget.getSjfktxsj(),projectBudget.getXmjsjd(),projectBudget.getSjfksj(),projectBudget.getXxzjbh(),projectBudget.getGlxmbh(),projectBudget.getYfkndh(),projectBudget.getYfkyf(),projectBudget.getYfkje(),projectBudget.getSjfkje()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public int deleteProjectBudget(String projectBudgetId){
		 String sql="update T_XM_FKJLB set djbgbs=3 where xxzjbh=?";
		 Object [] params={ projectBudgetId};
		 return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	  @Override
	 public int updateProjectBudget(ProjectBudget projectBudget){
	 	String sql="update T_XM_FKJLB set YFKTXR=?,YFKTXRSFHM=?,YFKTXRDWBM=?,YFKTXRSJHM=?,SJFKTXR=?,SJFKTXRSFHM=?,SJFKTXRDWBM=?,YFKTXSJ=?,SJFKTXSJ=?,XMJSJD=?,SJFKSJ=?,XXZJBH=?,GLXMBH=?,YFKNDH=?,YFKYF=?,YFKJE=?,SJFKJE=?  where xxzjbh=?";
	 	Object [] params={projectBudget.getYfktxr(),projectBudget.getYfktxrsfhm(),projectBudget.getYfktxrdwbm(),projectBudget.getYfktxrsjhm(),projectBudget.getSjfktxr(),projectBudget.getSjfktxrsfhm(),projectBudget.getSjfktxrdwbm(),projectBudget.getYfktxsj(),projectBudget.getSjfktxsj(),projectBudget.getXmjsjd(),projectBudget.getSjfksj(),projectBudget.getXxzjbh(),projectBudget.getGlxmbh(),projectBudget.getYfkndh(),projectBudget.getYfkyf(),projectBudget.getYfkje(),projectBudget.getSjfkje()};
	 	return this.getJdbcTemplate().update(sql.toString(),params);
	 }
	 
	 @Override
	 public List<Map<String, String>> findProjectBudgetByListAll(ProjectBudget projectBudget){
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		//查询条件
		if (projectBudget.getXxzjbh() != null&& !(projectBudget.getXxzjbh().equals(""))) {
			paramSql.append(" and xxzjbh=? ");
			params.add(projectBudget.getXxzjbh().trim());
		}
		String sql="select YFKTXR,YFKTXRSFHM,YFKTXRDWBM,YFKTXRSJHM,SJFKTXR,SJFKTXRSFHM,SJFKTXRDWBM,to_char(YFKTXSJ,'yyyy-MM-dd hh24:mi:ss') as YFKTXSJ,to_char(SJFKTXSJ,'yyyy-MM-dd hh24:mi:ss') as SJFKTXSJ,XMJSJD,SJFKSJ,XXZJBH,GLXMBH,YFKNDH,YFKYF,YFKJE,SJFKJE from T_XM_FKJLB where DJBGBS <>3  "+ paramSql.toString() ;
		List<Map<String, String>> list=new ArrayList<Map<String, String>>();
		try {
			list = this.getListBySql(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}